﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;


namespace DAO
{
    public class sqlConnnectionData
    {
        public static SqlConnection HamKetNoi()
        {
            SqlConnection cnn = new SqlConnection( @"Data Source=TRANTHANH-PC\SQLEXPRESS;Initial Catalog=BanSach;Integrated Security=True");
            return cnn;
        }
    }
    public class Sach_DAO
    {
        //load danh sach sach:
        public static DataTable LoadDSSach()
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("SelectAll_Sach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Them Sach Moi:
        public static void ThemSach(Sach_DTO Sach)
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("insert_Sach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@masach", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@tensach", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@Maloai", SqlDbType.Char, 10);
            cmd.Parameters.Add("@MaNXB", SqlDbType.Char, 10);
            cmd.Parameters.Add("@GiaNhap", SqlDbType.Float);
            cmd.Parameters.Add("@giaxuat", SqlDbType.Float);
            cmd.Parameters.Add("@SLCo", SqlDbType.Int);
            //gan:
            cmd.Parameters["@masach"].Value = Sach.MaSach;
            cmd.Parameters["@tensach"].Value = Sach.TenSach;
            cmd.Parameters["@MaLoai"].Value = Sach.MaLoaiSach;
            cmd.Parameters["@MaNXB"].Value = Sach.NXB;
            cmd.Parameters["@GiaNhap"].Value = Sach.GiaNhap;
            cmd.Parameters["@giaxuat"].Value = Sach.GiaXuat;
            cmd.Parameters["@SLCo"].Value = Sach.SLCo;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //Ban Sach:
        public static void BanSach(string masach, int soluong)
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("BanSach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@masach", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@n", SqlDbType.Int);
            cmd.Parameters["@masach"].Value = masach;
            cmd.Parameters["@n"].Value = soluong;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //SuaSach:
         public static void SuaSach(Sach_DTO sua)
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("Update_Sach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@masach", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@tensach", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@Maloai", SqlDbType.Char, 10);
            cmd.Parameters.Add("@MaNXB", SqlDbType.Char, 10);
            cmd.Parameters.Add("@GiaNhap", SqlDbType.Float);
            cmd.Parameters.Add("@giaxuat", SqlDbType.Float);
            cmd.Parameters.Add("@SLCo", SqlDbType.Int);
            //gan:
            cmd.Parameters["@masach"].Value = sua.MaSach;
            cmd.Parameters["@tensach"].Value = sua.TenSach;
            cmd.Parameters["@MaLoai"].Value = sua.MaLoaiSach;
            cmd.Parameters["@MaNXB"].Value = sua.NXB;
            cmd.Parameters["@GiaNhap"].Value = sua.GiaNhap;
            cmd.Parameters["@giaxuat"].Value = sua.GiaXuat;
            cmd.Parameters["@SLCo"].Value = sua.SLCo;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //XoaSach:
        public static void XoaSach(string masach)
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("Delete_Sach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@masach", SqlDbType.NChar, 10);
            cmd.Parameters["@masach"].Value = masach;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //TimTheLoai:
        public static DataTable TimTheLoai(string maTL)
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("Search_TheLoai", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maTL", SqlDbType.Char, 10);
            cmd.Parameters["@maTL"].Value = maTL;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //TimTenSach:
        public static DataTable TimTenSach(string tensach)
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("Search_TenSach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ten", SqlDbType.NVarChar, 50);
            cmd.Parameters["@ten"].Value = tensach;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //TimMaSach:
        public static DataTable TimMaSach(string masach)
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("Search_MaSach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ma", SqlDbType.Char, 10);
            cmd.Parameters["@ma"].Value = masach;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Tim MaNXB:
        public static DataTable TimNXB(string maNXB)
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("Search_MaNXB", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ma", SqlDbType.Char, 10);
            cmd.Parameters["@ma"].Value = maNXB;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
    }
    public class TheLoai_DAO
    {
        public static DataTable DanhSachTheLoai()
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("DanhSachLoaiSach ", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        public static void ThemTheLoai(LoaiSach_DTO loai)
        {
            SqlConnection cnn = sqlConnnectionData.HamKetNoi();
            SqlCommand cmd = new SqlCommand("ThemLoaiSach", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maloai", SqlDbType.Char, 10);
            cmd.Parameters.Add("@tenloai", SqlDbType.NVarChar, 50);
            cmd.Parameters["@maloai"].Value = loai.MaLoaiSach;
            cmd.Parameters["@tenloai"].Value = loai.TenLoaiSach;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }

    }
}
